package com.huidao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.huidao.entity.Role;
import com.huidao.entity.User;
import com.huidao.util.JDBCUtil;

public class UserDaoImpl implements UserDao {
	
	Connection conn = null;
	PreparedStatement ps =null;
	ResultSet rs =null;
	
	@Override
	public List<User> getUserList(int currentPage, int pageSize,
			String namekey, Integer roleId) {
		// TODO Auto-generated method stub
		
		String sql = "SELECT * FROM USER a " +
				" LEFT JOIN ROLE b ON a.ROLE_ID=b.ROLE_ID " +
				" WHERE 1=1 ";
		if(namekey!=null&&!"".equals(namekey)){
			sql = sql+" AND a.USER_NAME LIKE '%"+namekey+"%' ";
		}
		if(roleId!=null&&roleId!=0){
			sql = sql+" AND a.ROLE_ID="+roleId;
		}
		sql = sql +" LIMIT ?,?";
		
		List<User> list = new ArrayList<User>();
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (currentPage-1)*pageSize);
			ps.setInt(2, pageSize);
			rs =ps.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setUserId(rs.getLong("USER_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				user.setUserPassword(rs.getString("USER_PASSWORD"));
				user.setUserNickname(rs.getString("USER_NICKNAME"));
				user.setUserPhone(rs.getLong("USER_PHONE"));
				user.setUserTruename(rs.getString("USER_TRUENAME"));
				user.setUserIdnumber(rs.getString("USER_IDNUMBER"));
				user.setUserShopname(rs.getString("USER_SHOPNAME"));
				Role b = new Role();
				b.setRoleId(rs.getLong("ROLE_ID"));
				b.setRoleName(rs.getString("ROLE_NAME"));
				user.setRole(b);
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int getTotalCount(String namekey, Integer roleId) {
		String sql = "SELECT COUNT(*) FROM USER WHERE 1=1 ";
		if(namekey!=null&&!namekey.trim().equals("")){
			sql = sql +" AND USER_NAME LIKE '%"+namekey+"%' ";
		}
		if(roleId!=null&&roleId!=0){
			sql = sql+" AND ROLE_ID="+roleId;
		}
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs =ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}

	@Override
	public boolean addUser(User user) {
		String sql = "INSERT INTO USER(USER_NAME,USER_PASSWORD,USER_NICKNAME,USER_PHONE,USER_TRUENAME,USER_IDNUMBER,ROLE_ID,USER_SHOPNAME) VALUES(?,?,?,?,?,?,?,?)";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserPassword());
			ps.setString(3,user.getUserNickname());
			ps.setLong(4,user.getUserPhone());
			ps.setString(5,user.getUserTruename());
			ps.setString(6,user.getUserIdnumber());
			ps.setLong(7, user.getRole().getRoleId());
			ps.setString(8,user.getUserShopname());
			int i  =ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean delUser(Long id) {
		
		String sql = "DELETE FROM USER WHERE USER_ID=?";
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			int i  =ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateUser(User user) {
		String sql = "UPDATE USER SET USER_PASSWORD=?,USER_NICKNAME=?,USER_PHONE=?,USER_TRUENAME=?,USER_IDNUMBER=? " +
				"WHERE USER_ID=?";
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserPassword());
			ps.setString(2,user.getUserNickname());
			ps.setLong(3,user.getUserPhone());
			ps.setString(4,user.getUserTruename());
			ps.setString(5,user.getUserIdnumber());
			ps.setLong(6, user.getUserId());
			int i  =ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean isUserExist(User user) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM USER WHERE USER_NAME=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean checkPassword(User user) {
		String sql = "SELECT * FROM USER WHERE USER_NAME=? AND USER_PASSWORD=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserPassword());
			rs = ps.executeQuery();
			if(rs.next()){
				user.setUserId(rs.getLong("USER_ID"));
				Role role = new Role();
				role.setRoleId(rs.getLong("ROLE_ID"));
				user.setRole(role);
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public Long getRidByUsername(User user) {
		// TODO Auto-generated method stub
		String sql="select role_id from user where user_name=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return 0l;
	}


}
